package com.mybatisplus.demo.excel;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

public class Excel {

    public static void main(String[] args) throws Exception {
        long start = System.currentTimeMillis();

        //1.读取Excel文档对象 HSSFWorkbook
        List<Invoice> invoice = getInvoice("C:\\Users\\13327\\Desktop\\财务\\K3销售发票明细表.xlsx");


        List<Invoice> compare = getCompare("C:\\Users\\13327\\Desktop\\财务\\平台系统-自营明细表.xlsx");

        List<Invoice> collect = invoice.stream().map(i -> compare.stream().filter(c -> c.getConNum().equals(i.getConNum())
                && c.getSpecifications().equals(i.getSpecifications())
                && c.getQuantity() == i.getQuantity()
        ).findFirst().map(m -> {
            i.setStockIn(m.getStockIn());
            i.setCustName(m.getCustName());
            i.setSupplier(m.getSupplier());
            return i;
        }).orElse(null)).collect(Collectors.toList());

        List<Invoice> newInvoice = new ArrayList<>();
        List<Invoice> new1Invoice = new ArrayList<>();
        for (Invoice x : collect) {
            try {
                if (x.getCustName() != null) {
                    newInvoice.add(x);
                }
            } catch (Exception e) {
                new1Invoice.add(x);
                continue;
            }
        }

        Map<String, List<String>> map = map(newInvoice);
        String[] strArray = {"客户", "供应商", "产品代码", "产品名称",
                "规格型号", "单位", "数量", "实际含税单价",
                "含税采购单价", "价税合计", "单价",
                "销售收入", "合同号", "不含税采购单价", "不含税成本金额",
                "销售毛利", "销售毛利率"};

//        String[] strArray1 = {
//                "销售合同号+规格型号+数量", "销售合同号", "采购合同号",
//                "开票日期", "供应商", "客户",
//                "产品代码", "产品价格", "规格型号",
//                "单位", "数量", "实际含税单价",
//                "价税合计", "单价", "销售收入",
//                "含税采购单价", "不含税采购单价", "不含税成本金额",
//                "销售毛利", "销售毛利率"};

        createExcel(map, strArray, "匹配上的数据");
//        createExcel(map1, strArray,"未匹配上的数据");
        System.out.println(System.currentTimeMillis() - start);

    }

    public static Map<String, List<String>> map(List<Invoice> invoices) {
        Map<String, List<String>> map = new HashMap<>();
        for (int i = 0; i < invoices.size(); i++) {
            ArrayList<String> members = new ArrayList<String>();
            members.add(invoices.get(i).getCustName() + "");
            members.add(invoices.get(i).getSupplier() + "");
            members.add(invoices.get(i).getCode() + "");
            members.add(invoices.get(i).getProductName());
            members.add(invoices.get(i).getSpecifications() + "");
            members.add(invoices.get(i).getUnit() + "");


            members.add(invoices.get(i).getQuantity() + "");
            members.add(invoices.get(i).getPriceWithTax() + "");
            members.add(invoices.get(i).getStockIn() + "");
            members.add(invoices.get(i).getTotalMoneyWithTax() + "");
            members.add(invoices.get(i).getPrice() + "");
            members.add(invoices.get(i).getTotalMoney() + "");
            members.add(invoices.get(i).getConNum() + "");

            // TODO: 2019/12/26
            double j3 = invoices.get(i).getQuantity();
            BigDecimal o3 = invoices.get(i).getStockIn();
            BigDecimal n3 = invoices.get(i).getTotalMoney();

            BigDecimal p3 = o3.divide(new BigDecimal(1.13), 2, BigDecimal.ROUND_HALF_UP);
            //不含税采购单价
            members.add(p3 + "");
            //不含税成本金额
            BigDecimal q3 = o3.multiply(new BigDecimal(j3)).divide(new BigDecimal(1.13), 2, BigDecimal.ROUND_HALF_UP);

            members.add(o3.multiply(new BigDecimal(j3)).divide(new BigDecimal(1.13), 2, BigDecimal.ROUND_HALF_UP) + "");
            //销售毛利
            BigDecimal r3 = n3.subtract(q3).divide(new BigDecimal(1), 2, BigDecimal.ROUND_HALF_UP);
            members.add(r3 + "");
//            members.add(grossProfit + "");
            //销售毛利率
            NumberFormat percent = NumberFormat.getPercentInstance();
            percent.setMaximumFractionDigits(2);
            BigDecimal profit = r3.divide(n3, 4, BigDecimal.ROUND_HALF_UP);
            members.add(percent.format(profit.doubleValue()) + "");

            map.put(i + "", members);
        }

        return map;
    }

    public static List<Invoice> getInvoice(String path) throws Exception {
        XSSFWorkbook hssfWorkbook = new XSSFWorkbook(new FileInputStream(path));
        //2.获取要解析的表格（第一个表格）HSSFSheet
        XSSFSheet sheet = hssfWorkbook.getSheetAt(0);
        //获得最后一行的行号
        int lastRowNum = sheet.getLastRowNum();
        List<Invoice> invoices = new ArrayList<>();

        for (int i = 1; i <= lastRowNum - 1; i++) {//遍历每一行
            //3.获得要解析的行 HSSFRow
            XSSFRow row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            //4.获得每个单元格中的内容（String）
            Invoice invoice = new Invoice();
            try {
                invoice.setCode(row.getCell(3).getStringCellValue());
                invoice.setProductName(row.getCell(4).getStringCellValue());
                invoice.setSpecifications(row.getCell(5).getStringCellValue());
                invoice.setUnit(row.getCell(6).getStringCellValue());

                //数量
                double quantity = Double.parseDouble(row.getCell(7).toString());
                invoice.setQuantity(quantity);
                //含税采购单价
                invoice.setPriceWithTax(new BigDecimal(row.getCell(8).toString()));

                //含税采购单价
                BigDecimal priceWithTax = new BigDecimal(row.getCell(8).toString())
                        .divide(new BigDecimal(1.00), 2, BigDecimal.ROUND_HALF_UP);

                invoice.setStockIn(priceWithTax);

                //含税总金额
                BigDecimal totalMoneyWithTax = new BigDecimal(row.getCell(9).toString())
                        .divide(new BigDecimal(1.00), 2, BigDecimal.ROUND_HALF_UP);
                invoice.setTotalMoneyWithTax(totalMoneyWithTax);

                //单价
                BigDecimal price = new BigDecimal(row.getCell(10).toString())
                        .divide(new BigDecimal(1.00), 2, BigDecimal.ROUND_HALF_UP);
                invoice.setPrice(price);

                //销售总额
                BigDecimal totalMoney = new BigDecimal(row.getCell(11).getNumericCellValue())
                        .divide(new BigDecimal(1.00), 2, BigDecimal.ROUND_HALF_UP);
                invoice.setTotalMoney(totalMoney);

                //合同号
                String conNum = row.getCell(12).getStringCellValue().substring(0, 14);
                invoice.setConNum(conNum);


            } catch (Exception e) {
                e.printStackTrace();
                continue;
            }
            invoices.add(invoice);
        }
        return invoices;
    }

    public static List<Invoice> getCompare(String path) throws Exception {
        long start = System.currentTimeMillis();
        XSSFWorkbook hssfWorkbook = new XSSFWorkbook(new FileInputStream(path));
        //2.获取要解析的表格（第一个表格）HSSFSheet
        XSSFSheet sheet = hssfWorkbook.getSheetAt(0);
        //获得最后一行的行号
        int lastRowNum = sheet.getLastRowNum();
        List<Invoice> invoices = new ArrayList<>();

        for (int i = 1; i <= lastRowNum; i++) {//遍历每一行
            //3.获得要解析的行 HSSFRow
            XSSFRow row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            //4.获得每个单元格中的内容（String）
            Invoice invoice = new Invoice();
            try {
                invoice.setSupplier(row.getCell(5).getStringCellValue() + "");
                invoice.setSpecifications(row.getCell(9).getStringCellValue() + "");
                invoice.setStockIn(new BigDecimal(row.getCell(11).toString() + ""));
                invoice.setQuantity(Double.parseDouble(row.getCell(12).toString() + ""));
                invoice.setConNum(row.getCell(24).getStringCellValue().substring(0, 14) + "");
                invoice.setCustName(row.getCell(32).getStringCellValue() + "");

            } catch (Exception e) {
                System.out.println(e.getMessage());
                continue;
            }
            invoices.add(invoice);
        }
//        System.out.println((System.currentTimeMillis() - start));
        return invoices;
    }


    public static void createExcel(Map<String, List<String>> map, String[] strArray, String title) {

        // 第一步，创建一个webbook，对应一个Excel文件
        HSSFWorkbook wb = new HSSFWorkbook();
        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet("sheet1");
        sheet.setDefaultColumnWidth(20);// 默认列宽
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
        HSSFRow row = sheet.createRow((int) 0);
        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        // 创建一个居中格式
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        // 添加excel title
        HSSFCell cell = null;
        for (int i = 0; i < strArray.length; i++) {
            cell = row.createCell((short) i);
            cell.setCellValue(strArray[i]);
            cell.setCellStyle(style);
        }

        // 第五步，写入实体数据 实际应用中这些数据从数据库得到,list中字符串的顺序必须和数组strArray中的顺序一致
        int i = 0;
        for (String str : map.keySet()) {
            row = sheet.createRow((int) i + 1);
            List<String> list = map.get(str);

            // 第四步，创建单元格，并设置值
            for (int j = 0; j < strArray.length; j++) {
                if (j == 6) {
                    if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                        String pageViews = String.valueOf(list.get(6));
                        row.createCell(6).setCellValue(Double.parseDouble(pageViews));
                    }
                } else if (j == 7) {
                    if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                        String pageViews = String.valueOf(list.get(7));
                        row.createCell(7).setCellValue(Double.parseDouble(pageViews));
                    }
                } else if (j == 8) {
                    if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                        String pageViews = String.valueOf(list.get(8));
                        row.createCell(8).setCellValue(Double.parseDouble(pageViews));
                    }
                } else if (j == 9) {
                    if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                        String pageViews = String.valueOf(list.get(9));
                        row.createCell(9).setCellValue(Double.parseDouble(pageViews));
                    }
                } else if (j == 10) {
                    if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                        String pageViews = String.valueOf(list.get(10));
                        row.createCell(10).setCellValue(Double.parseDouble(pageViews));
                    }
                } else if (j == 11) {
                    if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                        String pageViews = String.valueOf(list.get(11));
                        row.createCell(11).setCellValue(Double.parseDouble(pageViews));
                    }
                } else if (j == 13) {
                    if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                        String pageViews = String.valueOf(list.get(13));
                        row.createCell(13).setCellValue(Double.parseDouble(pageViews));
                    }
                } else if (j == 14) {
                    if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                        String pageViews = String.valueOf(list.get(14));
                        row.createCell(14).setCellValue(Double.parseDouble(pageViews));
                    }
                } else if (j == 15) {
                    if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                        String pageViews = String.valueOf(list.get(15));
                        row.createCell(15).setCellValue(Double.parseDouble(pageViews));
                    }
                } else {
                    row.createCell((short) j).setCellValue(list.get(j));
                }
            }
            // 第六步，将文件存到指定位置
            try {
                FileOutputStream fout = new FileOutputStream("C:\\Users\\13327\\Desktop\\财务\\" + title + ".xls");
                wb.write(fout);
                fout.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
            i++;
        }
    }


}
